package com.cs2340.taskplusplus;

import java.text.DateFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class TaskDatabase 
{
	public static void newTask(Task t)
	{
		SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);
		db.execSQL("CREATE TABLE [" + t.getLabelTable() + "] (Parent_Table char(100), Name char(100));");
		for (Label l : t.getLabels())
		{
			db.execSQL("INSERT INTO [" + t.getLabelTable() + "] VALUES (\"" + t.getTableName() + "\", \""  + l.getName() + "\");");
		}
		db.close();
	}

	public static void newLabels(Task t)
	{
		SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);
		db.execSQL("DELETE FROM \"" + t.getLabelTable() + "\"");
		db.close();
	}

	public static void fillInfo(Task t)
	{
		SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db",null);
		Cursor info = db.query("["+t.parentTable+"]", new String[] {"Description", "Date", "Labels", "Latitude", "Longitude", "isDone"}, "Name=\""+t.name+"\"",null,null,null,null);
		info.moveToFirst();
		t.description = info.getString(info.getColumnIndex("Description"));
		t.labelTable = info.getString(info.getColumnIndex("Labels"));
		System.out.println("DATE");
		System.out.println(info.getString(info.getColumnIndex("Date")));
		t.date = new Date(info.getString(info.getColumnIndex("Date")));
		t.location = new Location(info.getInt(info.getColumnIndex("Latitude")), info.getInt(info.getColumnIndex("Longitude")));
		int a = info.getInt(info.getColumnIndex("isDone"));
		info.close();
		db.close();
		if (a > 0)
			t.setDone();
		else
			t.setNotDone();
		TaskDatabase.buildLabels(t);
	}
	
	public static void buildLabels(Task t) 
	{
		SQLiteDatabase db = android.database.sqlite.SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db", null);
		Cursor labelInfo = db.query("["+t.getLabelTable()+"]", new String[] {"Parent_Table","Name"}, null, null,null,null,null);
		labelInfo.moveToFirst();
		String labelName, labelParent;
		ArrayList<Label> labels = new ArrayList<Label>();
		for (int j = 0; j < labelInfo.getCount(); j++)
		{
			labelParent = labelInfo.getString(labelInfo.getColumnIndex("Parent_Table"));
			labelName = labelInfo.getString(labelInfo.getColumnIndex("Name"));
			labels.add(new Label(labelParent, labelName));
		}
		labelInfo.close();
		db.close();
		t.setLabels(labels);
	}
	
	public static void delete(Task t)
	{
		SQLiteDatabase db = android.database.sqlite.SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db", null);		
		if (t.getLabelTable() != null)
			db.execSQL("DROP TABLE \"" + t.getLabelTable() + "\"");
		db.close();
	}
	
	public static void setDone(Task t)
	{
		SQLiteDatabase db = android.database.sqlite.SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db", null);		
		db.execSQL("UPDATE ["+t.getParentTable() + "] SET isDone=1 WHERE Name=\""+t.getName()+"\"");
		db.close();
	}
	
	public static void setNotDone(Task t)
	{
		SQLiteDatabase db = android.database.sqlite.SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db", null);		
		db.execSQL("UPDATE ["+t.getParentTable() + "] SET isDone=0 WHERE Name=\""+t.getName()+"\"");
		db.close();
	}
	
	public static void expand(Task t)
	{
		SQLiteDatabase db = android.database.sqlite.SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db", null);		
		db.execSQL("UPDATE ["+t.getParentTable()+"] SET isList=1 WHERE Name='"+t.getName()+"'");
		db.close();
	}
	
	public static void addLabel(Task t, Label l)
	{
		SQLiteDatabase db = android.database.sqlite.SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db", null);		
		db.execSQL("INSERT INTO [" + t.getLabelTable() + "] VALUES (\""+t.getParentTable()+"\", \"" + l.getName() + "\");");
		db.close();
	}
	
	public static void removeLabel(Task t, Label l)
	{
		SQLiteDatabase db = android.database.sqlite.SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db", null);		
		db.execSQL("DELETE FROM [" + t.getLabelTable() + "] WHERE Name=\""+l.getName()+"\"");
		db.close();
	}	
	
	public static void setDate(Task t, Date d)
	{
		SQLiteDatabase db = android.database.sqlite.SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db", null);		
		db.execSQL("UPDATE [" + t.getParentTable() + "] SET Date=\"" + d.toString() + "\" WHERE Name=\""+t.getName()+"\"");
		db.close();
	}	
	
	public static void setLocation(Task t, Location loc) 
	{
		SQLiteDatabase db = android.database.sqlite.SQLiteDatabase.openOrCreateDatabase("/data/data/com.cs2340.taskplusplus/databases/data.db", null);
		db.execSQL("UPDATE ["+t.parentTable + "] SET Latitude=" + loc.getLat() + ", Longitude=" + loc.getLon() + " WHERE Name=\""+t.name+"\"");
		db.close();
	}
}